﻿<SQLStoredProcedureSpec
	xmlns="bb_appfx_sqlstoredprocedure"
	xmlns:common="bb_appfx_commontypes" 
	ID="81f9d871-029f-42f6-8fe3-8a89d9810be1"
	Name="Constituent Language Attribute View List"
	Description="Takes a constituent ID, lists all language attributes for that constituent"
	Author="Blackbaud Professional Services"
	SPName="USR_USP_CONSTITUENT_LANGUAGE_GETLIST"
	>

	<CreateProcedureSQL>
		<![CDATA[
create procedure USR_USP_CONSTITUENT_LANGUAGE_GETLIST(@CONSTITUENTID uniqueidentifier)
as
begin
  set nocount on;
  declare @LanguageAttributeTableName nvarchar(100) = dbo.USR_UFN_GETATTRIBUTETABLENAME('USR_ATTR_LANGUAGECODE','Language');
  declare @idlist table(constituentid uniqueidentifier, usr_attr_languagecodeid uniqueidentifier);
  insert into @idlist
  exec('select constituentid, usr_attr_languagecodeid from ' + @LanguageAttributeTableName);

  select
    con.name
   ,lang.description as language
   ,attrs.*
  from
    constituent con
    inner join
    @idlist attrs
    on con.id = attrs.constituentid
    inner join
    usr_attr_languagecode lang
    on lang.id = attrs.usr_attr_languagecodeid
  where con.id = @constituentid;
end;        
]]>
	</CreateProcedureSQL>
</SQLStoredProcedureSpec>